"""
Case Type   : GSC功能模块
Case Name   : database增删改操作，系统缓存信息相关视图函数功能验证
Create At   : 2023/05/10
Owner       : opentestcase024
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表并查询;
    step5:查看数据库syscache占用情况;
    step6:修改数据库名;
    step7:查看原库syscache占用情况;
    step8:连接并查询新库表;
    step9:查看新库syscache占用情况;
    step10:删除新库;
    step11:查看新库syscache占用情况;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表并查询; expect:操作成功
    step5:查看数据库syscache占用情况; expect:所有系统函数、视图缓存结果大于等于1
    step6:修改数据库名; expect:修改成功
    step7:查看原库syscache占用情况; expect:所有系统函数、视图缓存结果等于0
    step8:连接并查询新库表; expect:操作成功
    step9:查看新库syscache占用情况; expect:所有系统函数、视图缓存结果大于等于1
    step10:删除新库; expect:删除成功
    step11:查看新库syscache占用情况; expect:所有系统函数、视图缓存结果等于0
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
"""
import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.db_name = 'db_gsc0024'
        self.db_name1 = 'db_gsc0024_1'
        self.tb_name = 'tb_gsc0024'
        self.com = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')

        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = self.pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

    def test_main(self):
        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            msg = self.pri_sh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"enable_global_syscache= on")
            self.assertTrue(msg, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = self.pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = self.pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表并查询; expect:操作成功----'
        self.log.info(step_txt)
        create_tb_sql = f"create table {self.tb_name} " \
            f"(c_integer integer,c_tinyint tinyint," \
            f"c_smallint smallint,c_binary_integer binary_integer," \
            f"c_bigint bigint,c_numeric numeric(10,4)," \
            f"c_number number(10,4),c_smallserial smallserial);" \
            f"select * from {self.tb_name};"
        create_result = self.pri_sh.execut_db_sql(create_tb_sql,
                                                  dbname=f'{self.db_name}')
        self.log.info(create_result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, create_result,
                      '执行失败:' + step_txt)
        self.assertIn('0 rows', create_result, '执行失败:' + step_txt)

        step_txt = '----step5:查看数据库syscache占用情况; ' \
                   'expect:所有系统函数、视图缓存结果大于等于1----'
        self.log.info(step_txt)
        for check_item in self.check_cache(self.db_name):
            self.assertGreaterEqual(check_item, 1, '执行失败:' + step_txt)

        step_txt = '----step6:修改数据库名; expect:修改成功----'
        self.log.info(step_txt)
        alter_db = f'alter database {self.db_name} rename to {self.db_name1};'
        alter_result = self.pri_sh.execut_db_sql(alter_db)
        self.log.info(alter_result)
        self.assertIn(self.constant.ALTER_DATABASE_SUCCESS_MSG, alter_result,
                      '执行失败:' + step_txt)

        step_txt = '----step7:查看原库syscache占用情况; ' \
                   'expect:所有系统函数、视图缓存结果等于0----'
        self.log.info(step_txt)
        for check_item in self.check_cache(self.db_name):
            self.assertEqual(check_item, 0, '执行失败:' + step_txt)

        step_txt = '----step8:连接并查询新库表; expect:操作成功----'
        self.log.info(step_txt)
        select_sql = f"select * from {self.tb_name};"
        select_result = self.pri_sh.execut_db_sql(select_sql,
                                                  dbname=f'{self.db_name1}')
        self.log.info(select_result)
        self.assertIn('0 rows', select_result, '执行失败:' + step_txt)

        step_txt = '----step9:查看新库syscache占用情况; ' \
                   'expect:所有系统函数、视图缓存结果大于等于1----'
        self.log.info(step_txt)
        for check_item in self.check_cache(self.db_name1):
            self.assertGreaterEqual(check_item, 1, '执行失败:' + step_txt)

        step_txt = '----step10:删除新库; expect:删除成功----'
        self.log.info(step_txt)
        drop_db = f'drop database {self.db_name1};'
        drop_result = self.pri_sh.execut_db_sql(drop_db)
        self.log.info(drop_result)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      '执行失败:' + step_txt)

        step_txt = '----step11:查看新库syscache占用情况; ' \
                   'expect:所有系统函数、视图缓存结果等于0----'
        self.log.info(step_txt)
        for check_item in self.check_cache(self.db_name):
            self.assertEqual(check_item, 0, '执行失败:' + step_txt)

    def check_cache(self, db_name):
        """
        :param db_name: 查询指定库系统缓存结果
        :return: gs_gsc_memory_detail、gs_gsc_dbstat_info，
            gs_gsc_table_detail，gs_gsc_catalog_detail 缓存数量列表
        """
        db_oid = f"(select cast((select oid from pg_database where " \
            f"datname = '{db_name}') as text))"
        check_list_sql = [f"select count(*) from gs_gsc_memory_detail "
                          f"where db_id = ({db_oid});",
                          f"select count(*) from gs_gsc_dbstat_info() "
                          f"where database_name = '{db_name}';",
                          f"select count(*) from gs_gsc_table_detail() "
                          f"where database_name = '{db_name}';",
                          f"select count(*) from gs_gsc_catalog_detail() "
                          f"where database_name = '{db_name}';"]
        db_list = []
        for check_sql in check_list_sql:
            select_result = self.pri_sh.execut_db_sql(check_sql)
            self.log.info(select_result)
            db_list.append(int(select_result.splitlines()[-2].strip()))
        return db_list

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};" \
            f"drop database if exists {self.db_name1};"
        drop_result = self.pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = self.pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = self.pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            status = self.pri_sh.exec_cm_ctl(mode='query', param='-Cv')
            self.log.info(status)
            p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
            if 'Primary' in p_status and "cluster_state   : Normal" in status:
                self.log.info('---集群状态正常，无需恢复为初始状态---')
            else:
                recovery = self.pri_sh.exec_cm_ctl(mode='switchover',
                                                   param='-a')
                self.log.info(recovery)
                time.sleep(10)
            self.status_result = self.pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertEqual(
            drop_result.count(self.constant.DROP_DATABASE_SUCCESS), 2,
            "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
